package com.skp.shaphan.internalStorage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.skp.job.ProjectJobException;

public class DBConnector {
	private static DBConnector instance;
	private Connection connection;
	public static final String DATABASE_NAME = "db";
	
	private DBConnector() {
		try {
			connectToDatabase();
		} catch (SQLException e) {
			if(e.getMessage().equals("Database '" + DATABASE_NAME + "' not found.")) {
				try {
					createDatabase();
				} catch (SQLException e1) {
					(new ProjectJobException(e1)).displayException();
				}
			} else {
				(new ProjectJobException(e)).displayException();
			}
		}
		
		try {
			ArrayList<StructureVersion> versionList = buildVersionList();
			Version currentTargetVersion = versionList.get(versionList.size() - 1).getVersion();
			System.out.println("Current database version: " + currentTargetVersion);
			Version actualVersion = readVersion();
			if(actualVersion.compare(currentTargetVersion) > 0) {
				for(StructureVersion version : versionList) {
					if(actualVersion.compare(version.getVersion()) > 0) {
						version.updateDatabase(connection);
						stampDatabase(connection, version.getVersion());
					}
				}
			}
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
	}
	
	public static DBConnector getInstance() {
		if(instance == null) {
			instance = new DBConnector();
		}
		return instance;
	}
	
	public Connection getConnection() {
		return connection;
	}
	
	public void createDatabase() throws SQLException {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
		} catch (Exception e) {
			throw new SQLException("Derby Database is not installed correctly.", e);
		}
		connection = DriverManager.getConnection("jdbc:derby:" + DATABASE_NAME +";create=true");
		Statement st = connection.createStatement();
		st.execute("create table " + Tables.DBConnections + " (" +
				Fields.Connection_Id +" int," +
				Fields.Connection_Name + " varchar(30)," +
				Fields.Connection_TypeClass + " varchar(100)," +
				Fields.Connection_TypeName + " varchar(50)," +
				"primary key(" + Fields.Connection_Id + "))");
		st.execute("create table " + Tables.DBConnectionProperties + " (" +
				Fields.CProperty_ConnectionId + " int," +
				Fields.CProperty_PropertyId + " int," +
				Fields.CProperty_Name + " varchar(30)," +
				Fields.CProperty_Value + " varchar(255)," +
				"primary key(" + Fields.CProperty_ConnectionId + ", " + Fields.CProperty_PropertyId + "))");
		st.close();
		
		ArrayList<StructureVersion> versionList = buildVersionList();
		for(StructureVersion version : versionList) {
			version.updateDatabase(connection);
			stampDatabase(connection, version.getVersion());
		}
	}
	
	private void connectToDatabase() throws SQLException {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
		} catch (Exception e) {
			throw new SQLException("Derby Database is not installed correctly.", e);
		}
		connection = DriverManager.getConnection("jdbc:derby:" + DATABASE_NAME);
	}
	
	public Version readVersion() {
		Connection conn = getConnection();
		Version version = new Version();
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select * from " + Tables.VersionTable);
			if(rs.next()) {
				version.setMajor(rs.getInt(Fields.Ver_Major));
				version.setMinor(rs.getInt(Fields.Ver_Minor));
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
		}
		return version;
	}
	
	public ArrayList<StructureVersion> buildVersionList() {
		ArrayList<StructureVersion> list = new ArrayList<StructureVersion>();
		list.add(new Version_0_1());
		list.add(new Version_0_2());
		return list;
	}
	
	private void stampDatabase(Connection connection, Version version) throws SQLException {
		Statement st = connection.createStatement(ResultSet.CONCUR_UPDATABLE, ResultSet.FETCH_FORWARD);
		ResultSet rs = st.executeQuery("select * from " + Tables.VersionTable);
		boolean inserting = false;
		if(!rs.next()) {
			rs.moveToInsertRow();
			inserting = true;
		}
		rs.updateInt(Fields.Ver_Major, version.getMajor());
		rs.updateInt(Fields.Ver_Minor, version.getMinor());
		if(inserting) {
			rs.insertRow();
		} else {
			rs.updateRow();
		}
		rs.close();
		st.close();
		
		System.out.println("updating database to version: " + version);
	}
}
